package io.renren.modules.report.controller;

import java.sql.*;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.function.Consumer;
import java.util.stream.Collectors;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import io.renren.common.utils.GenUtils;
import io.renren.modules.report.entity.*;
import io.renren.modules.report.service.BusinessPackageService;
import io.renren.modules.report.service.ConnectionService;
import io.renren.modules.report.vo.*;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import io.renren.modules.report.service.DataPackageService;
import io.renren.common.utils.PageUtils;
import io.renren.common.utils.R;


/**
 * 
 *
 * @author august
 * @email 379249906@qq.com
 * @date 2020-07-10 08:41:14
 */
@RestController
@RequestMapping("report/datapackage")
public class DataPackageController {
    @Autowired
    private DataPackageService dataPackageService;

    @Autowired
    private BusinessPackageService businessPackageService;

    @Autowired
    private ConnectionService connectionService;

    /**
     * 列表
     */
    @RequestMapping("/list")
    public R list(@RequestParam Map<String, Object> params){
        PageUtils page = dataPackageService.queryPage(params);

        return R.ok().put("page", page);
    }

    @RequestMapping("/list/{userId}")
    public R dataPackageList(@PathVariable("userId") Long userId){

        List<DataPackageEntity> dataPackageEntities = dataPackageService.list(new QueryWrapper<DataPackageEntity>().eq("user_id", userId));


        List<SelectedVO> collect = dataPackageEntities.stream().map(attr -> {
            SelectedVO selectedVO = new SelectedVO();
            selectedVO.setLabel(attr.getDataName());
            selectedVO.setValue(attr.getId());
            return selectedVO;
        }).collect(Collectors.toList());


        return R.ok().put("dataPackageList", collect);
    }

    /**
     * 信息
     */
    @RequestMapping("/info/{id}")
    public R info(@PathVariable("id") Long id){
		DataPackageEntity dataPackage = dataPackageService.getById(id);

        return R.ok().put("dataPackage", dataPackage);
    }

    /**
     * 通过数据包id，当前数据包下被选择的表
     * @param dataId
     * @return
     */
    @RequestMapping("/tabls/{dataId}")
    public R databaseInfoByDataId(@PathVariable("dataId") Long dataId) {
        DataPackageEntity dataPackageEntity = dataPackageService.getById(dataId);
        String tableName = dataPackageEntity.getTableName();
        AtomicInteger key= new AtomicInteger(1);
        List<TransferVO> collect = Arrays.asList(tableName.split(",")).stream().map(item -> {
            TransferVO transferVO = new TransferVO();
            transferVO.setKey(key.getAndIncrement());
            transferVO.setLabel(item);
            return transferVO;
        }).collect(Collectors.toList());

        return R.ok().put("selectedTableNames", collect);
    }

    /**
     *  数据包预览
     * @return
     */
    @PostMapping("/preview")
    public R preview(@RequestBody PreviewVO previewVO){
        DataPackageEntity entity = dataPackageService.getById(previewVO.getDataId());
        String tableName = entity.getTableName();
        ArrayList<HashMap<String,String>> selectedColumns = new ArrayList<>();

        StringBuffer fieldsBuffere=new StringBuffer();

        AtomicInteger index= new AtomicInteger(0);
        previewVO.getTableColumns().forEach((item)->{
             String fileds = GenUtils.propertyToField(item);
             if(index.get()>0){
                 fieldsBuffere.append(","+fileds);
             }else{
                 fieldsBuffere.append(fileds);
            }
             index.getAndIncrement();
          }
        );

        System.out.println("fieldsBuffere: "+fieldsBuffere);

        Connection conn = this.getConnByBusinessId(entity.getBPId());
        //得到链接
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            String querySql="select " +fieldsBuffere.toString()+
                    " from " +tableName;
            ResultSet rs = stmt.executeQuery(querySql);
            while (rs.next()) {
                HashMap<String, String> hashMap = new HashMap<>();
                //String username = rs.getString("username");
                for (int i = 0; i <previewVO.getTableColumns().size() ; i++) {
                    hashMap.put(previewVO.getTableColumns().get(i),rs.getString(previewVO.getTableColumns().get(i)));
                }
                selectedColumns.add(hashMap);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return R.ok().put("selectedColumns", selectedColumns);
    }


    /**
     * 通过数据包id和表名，获取表中的columns信息
     * @param
     * @return
     */
    @RequestMapping("/columns/{dataId}")
    public R Columns(@PathVariable("dataId") Long dataId){
        DataPackageEntity entity = dataPackageService.getById(dataId);
        String tableName=entity.getTableName();
        ArrayList<ColumnEntity> columnEntities = new ArrayList<>();
        //得到链接
        Statement stmt = null;
        Connection conn = this.getConnByBusinessId(entity.getBPId());
        try {
            stmt = conn.createStatement();

            String queryColumnsSql="select column_name columnName, data_type dataType, column_comment columnComment, column_key columnKey, extra from information_schema.columns\n" +
                    " where table_name = " +"'"+
                    tableName+"'"+
                    " and table_schema = (select database()) order by ordinal_position";
            System.out.println("queryColumnsSql: "+queryColumnsSql);
            ResultSet rs = stmt.executeQuery(queryColumnsSql);
            while (rs.next()) {
                ColumnEntity columnEntity = new ColumnEntity();
                columnEntity.setColumnName(rs.getString("columnName"));
                columnEntity.setDataType(rs.getString("dataType"));
                columnEntity.setColumnComment(rs.getString("columnComment"));
                columnEntity.setColumnKey(rs.getString("columnComment"));
                columnEntity.setColumnKey(rs.getString("columnKey"));
                columnEntity.setExtra(rs.getString("extra"));
                columnEntities.add(columnEntity);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return R.ok().put("columns", columnEntities);
    }

    private Connection getConnByBusinessId(Long bussinessId){
        BusinessPackageEntity businessPackageEntity = businessPackageService.getById(bussinessId);
        ConnectionEntity connectEntity = connectionService.getById(businessPackageEntity.getConnectionId());
        String databaseName = connectEntity.getDatabaseName();
        String driverName=connectEntity.getDriverName();
        String connectUsername = connectEntity.getConnectUsername();
        String connectPassword = connectEntity.getConnectPassword();
        String connectUrl="";
        String driverUrl="";
        if("mysql".equalsIgnoreCase(driverName)){
            driverUrl="com.mysql.cj.jdbc.Driver";
            //jdbc:mysql://localhost:3306/renren_fast?useUnicode=true&characterEncoding=UTF-8
            connectUrl="jdbc:mysql://"+connectEntity.getIp()+":"+connectEntity.getPort()+"/"
                    +databaseName+"?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
        }
        Connection conn = null;
        Statement stmt = null;

        try {
            Class.forName(driverUrl);
            conn = DriverManager.getConnection(connectUrl, connectUsername, connectPassword);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return conn;
    }

    /**
     * 通过业务包id，获取所有表
     * @param packageId
     * @return
     */
    @RequestMapping("/allTables/{packageId}")
    public R databaseInfo(@PathVariable("packageId") Long packageId) {
        ArrayList<String> tableNames = new ArrayList<>();

        //1 通过dataId找到对应的链接信息
        BusinessPackageEntity businessPackageEntity = businessPackageService.getById(packageId);
        ConnectionEntity connectEntity = connectionService.getById(businessPackageEntity.getConnectionId());
        String databaseName = connectEntity.getDatabaseName();
        System.out.println(connectEntity.getDriverName());

        String driverName=connectEntity.getDriverName();
        String connectUsername = connectEntity.getConnectUsername();
        String connectPassword = connectEntity.getConnectPassword();
        String connectUrl="";
        String driverUrl="";
        if("mysql".equalsIgnoreCase(driverName)){
            driverUrl="com.mysql.cj.jdbc.Driver";
            //jdbc:mysql://localhost:3306/renren_fast?useUnicode=true&characterEncoding=UTF-8
            connectUrl="jdbc:mysql://"+connectEntity.getIp()+":"+connectEntity.getPort()+"/"
                    +databaseName+"?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
        }

        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName(driverUrl);
            conn = DriverManager.getConnection(connectUrl, connectUsername, connectPassword);
            System.out.println("成功获取数据库连接");
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select table_name tableName, engine, table_comment tableComment, create_time createTime from information_schema.tables\n" +
                    "where table_schema = (select database())\n" +
                    "order by create_time desc");
            while (rs.next()) {
                String tableName = rs.getString("tableName");
                tableNames.add(tableName);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        List<SelectedVO> collect = connectionService.list().stream().map(item -> {
            SelectedVO selectedVO = new SelectedVO();
            selectedVO.setValue(item.getId());
            selectedVO.setLabel(item.getName());
            return selectedVO;
        }).collect(Collectors.toList());

        AtomicInteger key= new AtomicInteger(1);
        List<Object> collectTableNames = tableNames.stream().map(item -> {
            TransferVO transferVO = new TransferVO();
            transferVO.setKey(key.getAndIncrement());
            transferVO.setLabel(item);
            return transferVO;
        }).collect(Collectors.toList());

        return R.ok().put("tableNames", collectTableNames);

    }


    /**
     * 保存
     */
    @RequestMapping("/save")
    public R save(@RequestBody DataPackageVO dataPackageVO){
        List<DataPackageEntity> collect = dataPackageVO.getTransfers().stream().map(item -> {
            DataPackageEntity dataPackageEntity = new DataPackageEntity();
            dataPackageEntity.setBPId(dataPackageVO.getBussinessId());
            dataPackageEntity.setUserId(dataPackageVO.getUserId());
            dataPackageEntity.setTableName(item.getLabel());
            dataPackageEntity.setDataName(item.getLabel());
            return dataPackageEntity;
        }).collect(Collectors.toList());
        dataPackageService.saveBatch(collect);
        return R.ok();
    }

    /**
     * 图形渲染
     * @param
     * @return
     */
    @PostMapping("/render")
    public R render(@RequestBody RenderVO renderVO){
        String dataId = renderVO.getDataId();
        DataPackageEntity dataPackage = dataPackageService.getById(dataId);
        Long bpId = dataPackage.getBPId();

        ArrayList<HashMap<String,String>> selectedColumns = new ArrayList<>();

        //表名
        String tableName = dataPackage.getTableName();
        //查询的字段
        String queryString=renderVO.getVertical();
        //group by字段
        String horizontalStr = renderVO.getHorizontal();
        //排序字段
        String sorted=renderVO.getSort();

        //得到链接
        Statement stmt = null;
        Connection conn = this.getConnByBusinessId(bpId);

        try {
            stmt = conn.createStatement();

            String queryColumnsSql="select sum(" +queryString+
                    ") " +queryString+
                    ",DATE_FORMAT(" +horizontalStr+
                    ",'%m') " +horizontalStr+
                    " from " +tableName+
                    " GROUP BY DATE_FORMAT(" +horizontalStr+
                    ",'%Y%m')";

            System.out.println("queryColumnsSql---"+queryColumnsSql);
            ResultSet rs = stmt.executeQuery(queryColumnsSql);
            while (rs.next()) {
                HashMap<String, String> hashMap = new HashMap<>();
                //String username = rs.getString("username");
                String queryValue = rs.getString(queryString);
                String horizontalValue = rs.getString(horizontalStr);
                hashMap.put(queryString,queryValue);
                hashMap.put(horizontalStr,horizontalValue);
                selectedColumns.add(hashMap);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }


//        BusinessPackageEntity businessPackageEntity = businessPackageService.getById(bpId);
//        ConnectionEntity connectEntity = connectionService.getById(businessPackageEntity.getConnectionId());
//        String databaseName = connectEntity.getDatabaseName();
//        System.out.println(connectEntity.getDriverName());

        return R.ok().put("columns", selectedColumns);
    }

    /**
     * 修改
     */
    @RequestMapping("/update")
    public R update(@RequestBody DataPackageUpdateVO dataPackageUpdateVO){
        DataPackageEntity dataPackage = dataPackageService.getById(dataPackageUpdateVO.getDataPackageId());
        String columns = StringUtils.join(dataPackageUpdateVO.getColumns(),",");
        dataPackage.setColumnName(columns);
        dataPackageService.update(dataPackage,new UpdateWrapper<DataPackageEntity>().eq("id",dataPackageUpdateVO.getDataPackageId()));
        return R.ok();
    }

    /**
     * 删除
     */
    @RequestMapping("/delete")
    public R delete(@RequestBody Long[] ids){
		dataPackageService.removeByIds(Arrays.asList(ids));

        return R.ok();
    }

}
